/**
 * 
 */
package dbAccess;

import java.sql.*;
import java.util.*;

import exceptions.*;

import model.*;

/**
 * @author Anthares & Hennek
 *
 */
public class RechercheDBAccess {
	public ArrayList<Etudiant> rechercheEtudiant(int syllabus_id, Boolean paye, String etat) throws PasIdentificationException, DatabaseErrorException{
		ArrayList<Etudiant> tabEtu = new ArrayList<Etudiant>();
		SectionDBAccess sectdba = new SectionDBAccess();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query = 	 "SELECT * "
						+"FROM syllabus, ligne_com_etu, commande_etu, etudiant, section "
						+"WHERE syllabus.syllabus_id = ? AND syllabus.syllabus_id=ligne_com_etu.syllabus_id AND ligne_com_etu.commande_etu_id=commande_etu.commande_etu_id AND commande_etu.etudiant_id=etudiant.etudiant_id AND etudiant.section_id=section.section_id;";
		String queryEtat =	 "SELECT * "
						+"FROM syllabus, ligne_com_etu, commande_etu, etudiant, section "
						+"WHERE syllabus.syllabus_id = ? AND syllabus.syllabus_id=ligne_com_etu.syllabus_id AND ligne_com_etu.commande_etu_id=commande_etu.commande_etu_id AND commande_etu.etudiant_id=etudiant.etudiant_id AND etudiant.section_id=section.section_id "
						+"AND commande_etu.etat = ?;";
		String queryPaye =	 "SELECT * "
							+"FROM syllabus, ligne_com_etu, commande_etu, etudiant, section "
							+"WHERE syllabus.syllabus_id = ? AND syllabus.syllabus_id=ligne_com_etu.syllabus_id AND ligne_com_etu.commande_etu_id=commande_etu.commande_etu_id AND commande_etu.etudiant_id=etudiant.etudiant_id AND etudiant.section_id=section.section_id "
							+"AND commande_etu.paye = ?;";
		String queryEtatPaye =	 "SELECT * "
								+"FROM syllabus, ligne_com_etu, commande_etu, etudiant, section "
								+"WHERE syllabus.syllabus_id = ? AND syllabus.syllabus_id=ligne_com_etu.syllabus_id AND ligne_com_etu.commande_etu_id=commande_etu.commande_etu_id AND commande_etu.etudiant_id=etudiant.etudiant_id AND etudiant.section_id=section.section_id "
								+"AND commande_etu.etat = ? "
								+"AND commande_etu.paye = ?;";
		try {
			if(paye!=null || etat!=null){
				if(etat==null){
					stat = conn.prepareStatement(queryPaye);
					stat.setInt(1, syllabus_id);
					stat.setBoolean(2, paye);
				}
				else{
					if(paye==null){
						stat = conn.prepareStatement(queryEtat);
						stat.setInt(1, syllabus_id);
						stat.setString(2, etat);
					}
					else{
						stat = conn.prepareStatement(queryEtatPaye);
						stat.setInt(1, syllabus_id);
						stat.setString(2, etat);
						stat.setBoolean(3, paye);
					}
				}
			}
			else{
				stat = conn.prepareStatement(query);
				stat.setInt(1, syllabus_id);
			}
			
			ResultSet data = stat.executeQuery();
			while(data.next()){
				Etudiant e = new Etudiant(data.getInt("etudiant_id"), data.getString("nom"), data.getString("prenom"), sectdba.getSection(data.getInt("section_id")), data.getInt("annee"));
				
				String mat = data.getString("matricule");
				if(data.wasNull()==false) e.setMatricule(mat);
				
				String pseudo = data.getString("pseudo");
				if(data.wasNull()==false) e.setPseudo(pseudo);
				
				tabEtu.add(e);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		
		
		return tabEtu;
	}
	public ArrayList<Commande_etu> rechercheSyllabusEtudiant(String etu) throws PasIdentificationException, DatabaseErrorException{
		ArrayList<Commande_etu> tabCom = new ArrayList<Commande_etu>();
		SectionDBAccess sectdba = new SectionDBAccess();
		OrderStatusDBAccess osdba = new OrderStatusDBAccess();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query = 	 "SELECT *, etudiant.nom AS etunom, etudiant.prenom as etuprenom, etudiant.annee as etuannee, syllabus.annee as sylannee, professeur.nom as profnom, professeur.prenom as profprenom, syllabus.section_id as syllsection, etudiant.section_id ad etusection "
						+"FROM etudiant, commande_etu, ligne_com_etu, syllabus, section, auteur, professeur "
						+"WHERE (etudiant.nom LIKE ? OR etudiant.prenom LIKE ?) "
						+"AND etudiant.etudiant_id=commande_etu.etudiant_id "
						+"AND commande_etu.commande_etu_id=ligne_com_etu.commande_etu_id "
						+"AND ligne_com_etu.syllabus_id=syllabus.syllabus_id "
						+"AND etudiant.section_id=section.section_id "
						+"AND syllabus.syllabus_id=auteur.syllabus_id "
						+"AND auteur.professeur_id=professeur.professeur_id;";
		try {
			stat = conn.prepareStatement(query);
				stat.setString(1, "%"+etu+"%");
				stat.setString(2, "%"+etu+"%");
			ResultSet data = stat.executeQuery();
			while(data.next()){
				Etudiant e = new Etudiant(data.getInt("etudiant_id"), data.getString("etunom"), data.getString("etuprenom"), sectdba.getSection(data.getInt("etusection")), data.getInt("etuannee"));
				
				String mat = data.getString("matricule");
				if(data.wasNull()==false) e.setMatricule(mat);
				
				String pseudo = data.getString("pseudo");
				if(data.wasNull()==false) e.setPseudo(pseudo);
				
				java.sql.Date dat = data.getDate("date_commande_etu");
				java.util.Date gc = new java.util.Date();
				gc.setTime(dat.getTime());
				
				Commande_etu com = new Commande_etu(data.getInt("commande_etu_id"),gc , e, null, osdba.getOrderStatus(data.getInt("etat")), data.getBoolean("paye"));
				// TODO recuperer reliure et livraison depuis ligne_com_etu !!
				Syllabus s = new Syllabus(data.getInt("syllabus_id"), data.getString("intitule"), sectdba.getSection(data.getInt("syllsection")), data.getInt("sylannee"), data.getInt("nb_page"), true, false);
				Professeur prof = new Professeur(data.getInt("professeur_id"), data.getString("profnom"), data.getString("profprenom"));
				s.setAuteur(prof);
				com.addSyllabus(s);
				tabCom.add(com);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return tabCom;
	}
	public ArrayList<Syllabus> rechercheSyllabusDispo(Section sect, int annee) throws PasIdentificationException, DatabaseErrorException{
		ArrayList<Syllabus> tabSyl = new ArrayList<Syllabus>();
		SectionDBAccess sectdba = new SectionDBAccess();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query = 	"SELECT * "+
						"FROM section, syllabus, auteur, professeur " +
						"WHERE section.libelle = ? " +
						"AND section.section_id=syllabus.section_id " +
						"AND syllabus.annee = ? " +
						"AND syllabus.syllabus_id=auteur.syllabus_id " +
						"AND auteur.professeur_id=professeur.professeur_id;";
		try {
			stat = conn.prepareStatement(query);
				stat.setString(1, sect.getLibelle());
				stat.setInt(2, annee);
			ResultSet data = stat.executeQuery();
			while (data.next()) {
				Professeur p = new Professeur(data.getInt("professeur_id"), data.getString("nom"), data.getString("prenom"));
				Section secti= sectdba.getSection(data.getInt("section_id"));
				Syllabus s = new Syllabus(data.getInt("syllabus_id"), data.getString("intitule"), secti, data.getInt("annee"), data.getInt("nb_page"), true, false);
				s.setAuteur(p);
				tabSyl.add(s);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return tabSyl;
	}
}
